﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
//using System.Data.OracleClient;
using System.Data;
using System.Web;
using System.ServiceModel.Activation;
using System.Threading.Tasks;
//using System.Windows;
//using System.Windows.Threading;
using Oracle.DataAccess.Client;
namespace SilverlightOracle.Web
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service" in code, svc and config file together.
    //[ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(IncludeExceptionDetailInFaults = true)]
    public class Service : IService
    {
        HttpContext myctxt = HttpContext.Current;

        Conn connection = new Conn();
        public List<truong> GetTruong()
        {
            List<truong> member = new List<truong>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "GetTruong.GetTruongBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("cur_GetTruong", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                truong school = new truong();
                school._matruong = Convert.ToString(reader["MATRUONG"]);
                school._tentruong = Convert.ToString(reader["TENTRUONG"]);
                member.Add(school);
            }
            return member.ToList();
        }
        public List<thisinh> SreachThiSinh(string MATRUONG, string MASV, string HOTEN)
        {
            List<thisinh> member = new List<thisinh>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "SearchThiSinh.SearchThiSinhBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ho_ten", OracleDbType.Varchar2).Value = HOTEN;
            cmd.Parameters.Add("ma_sinhvien", OracleDbType.Varchar2).Value = MASV;
            cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = MATRUONG;
            cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                thisinh student = new thisinh();
                student._masv = Convert.ToString(reader["MASV"]);
                student._mon1 = Convert.ToDouble(reader["MON1"]);
                student._mon2 = Convert.ToDouble(reader["MON2"]);
                student._mon3 = Convert.ToDouble(reader["MON3"]);
                student._ngaysinh = Convert.ToDateTime(reader["NGAYSINH"]).ToShortDateString();
                student._truong = Convert.ToString(reader["TENTRUONG"]);
                student._diemuutien = Convert.ToDouble(reader["DIEMUUTIEN"]);
                student._hoten = Convert.ToString(reader["HOTEN"]);
                member.Add(student);
            }
            return member.ToList();

        }
        public List<nguoidung> ShowNguoiDung(int MATAIKHOAN, string TENTAIKHOAN, string QUYEN)
        {
            List<nguoidung> member = new List<nguoidung>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            if (MATAIKHOAN != 0)
            {
                cmd.CommandText = "ShowNguoiDungMaTaiKhoan.ShowNguoiDungMaTaiKhoanBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("ma_taikhoan", OracleDbType.Int32).Value = MATAIKHOAN;
                cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

            }
            else
            {
                if (TENTAIKHOAN != "")
                {
                    cmd.CommandText = "ShowNguoiDungTenTaiKhoan.ShowNguoiDungTenTaiKhoanBy";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("ten_taikhoan", OracleDbType.Varchar2).Value = TENTAIKHOAN;
                    cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                }
                else
                {
                    cmd.CommandText = "ShowNguoiDungQuyen.ShowNguoiDungQuyenBy";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("quyen_truycap", OracleDbType.Varchar2).Value = QUYEN;
                    cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                }
            }
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                nguoidung user = new nguoidung();
                user._taikhoan = Convert.ToString(reader["TAIKHOAN"]);
                user._matkhau = Convert.ToString(reader["MATKHAU"]);
                user._manguoidung = Convert.ToInt32(reader["MANGUOIDUNG"]);
                user._quyentruycap = Convert.ToString(reader["TENQUYEN"]);
                user._email = Convert.ToString(reader["EMAIL"]);
                member.Add(user);
            }
            return member.ToList();
        }
        public bool InsertNguoiDung(int MATAIKHOAN, string TENTAIKHOAN, string MATKHAU, string QUYENTRUYCAP, string EMAIL)
        {
            bool Ins = false;
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "InsertNguoiDung.InsertNguoiDungBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("tai_khoan", OracleDbType.Varchar2).Value = TENTAIKHOAN;
            cmd.Parameters.Add("mat_khau", OracleDbType.Varchar2).Value = MATKHAU;
            cmd.Parameters.Add("ma_tai_khoan", OracleDbType.Int32).Value = MATAIKHOAN;
            cmd.Parameters.Add("quyen_truy_cap", OracleDbType.Varchar2).Value = QUYENTRUYCAP;
            cmd.Parameters.Add("email_dk", OracleDbType.Varchar2).Value = EMAIL;
            int insert = cmd.ExecuteNonQuery();

            if (insert > 0)
            {
                Ins = true;
            }
            return Ins;
        }
        public bool UpdateNguoiDung(nguoidung user, string QUYEN)
        {
            bool Upd = false;
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "UpdateNguoiDung.UpdateNguoiDungBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("tai_khoan", OracleDbType.Varchar2).Value = user._taikhoan;
            cmd.Parameters.Add("mat_khau", OracleDbType.Varchar2).Value = user._matkhau;
            cmd.Parameters.Add("ma_tai_khoan", OracleDbType.Int32).Value = user._manguoidung;
            cmd.Parameters.Add("quyen_truy_cap", OracleDbType.Varchar2).Value = QUYEN;
            cmd.Parameters.Add("email_dk", OracleDbType.Varchar2).Value = user._email;
            int insert = cmd.ExecuteNonQuery();

            if (insert > 0)
            {
                Upd = true;
            }
            return Upd;
        }
        public bool UpdateTruong(detailtruong school, string MANGUOIDUNG)
        {
            bool Upd = false;
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "UpdateTruong.UpdateTruongBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = school._matruong;

            OracleCommand cmd1 = new OracleCommand();
            cmd1.Connection = connect;
            cmd1.CommandText = "InsertTruong.InsertTruongBy";
            cmd1.CommandType = CommandType.StoredProcedure;
            cmd1.Parameters.Add("ten_truong", OracleDbType.Varchar2).Value = school._tentruong;
            cmd1.Parameters.Add("email_dk", OracleDbType.Varchar2).Value = school._email;
            cmd1.Parameters.Add("dien_thoai", OracleDbType.Varchar2).Value = school._dienthoai;
            cmd1.Parameters.Add("ma_nguoi_dung", OracleDbType.Varchar2).Value = MANGUOIDUNG;
            cmd1.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now;
            cmd1.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = school._matruong;

            int update = cmd.ExecuteNonQuery();
            int insert = cmd1.ExecuteNonQuery();


            if (insert > 0 && update > 0)
            {
                Upd = true;
            }
            return Upd;
        }
        public string InsertTruong(string MATRUONG, string TENTRUONG, string DIENTHOAI, string EMAIL, string MANGUOIDUNG)
        {
            string Ins = "";
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "InsertTruong.InsertTruongBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ten_truong", OracleDbType.Varchar2).Value = TENTRUONG;
            cmd.Parameters.Add("email_dk", OracleDbType.Varchar2).Value = EMAIL;
            cmd.Parameters.Add("dien_thoai", OracleDbType.Varchar2).Value = DIENTHOAI;
            cmd.Parameters.Add("ma_nguoi_dung", OracleDbType.Varchar2).Value = MANGUOIDUNG;
            cmd.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now;
            cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = MATRUONG;
            int insert = cmd.ExecuteNonQuery();

            if (insert > 0)
            {
                Ins = MATRUONG;
            }
            return Ins;
        }
        public List<detailtruong> ShowTruong(string MATRUONG, string TENTRUONG, int MANGUOIDUNG)
        {
            List<detailtruong> member = new List<detailtruong>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "ShowTruong.ShowTruongBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = MATRUONG;
            cmd.Parameters.Add("ten_truong", OracleDbType.Varchar2).Value = TENTRUONG;
            cmd.Parameters.Add("ma_nguoidung", OracleDbType.Int32).Value = MANGUOIDUNG;
            cmd.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                detailtruong user = new detailtruong();
                user._matruong = Convert.ToString(reader["MATRUONG"]);
                user._tentruong = Convert.ToString(reader["TENTRUONG"]);
                user._email = Convert.ToString(reader["EMAIL"]);
                user._dienthoai = Convert.ToString(reader["DIENTHOAI"]);
                user._manguoidung = Convert.ToString(reader["MANGUOIDUNG"]);
                user._ngaycapnhat = Convert.ToDateTime(reader["NGAYCAPNHAT"]);
                member.Add(user);
            }
            return member.ToList();
        }
        public List<detailthisinh> ShowThiSinh()
        {
            List<detailthisinh> member = new List<detailthisinh>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("cur_JobHistory", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                detailthisinh student = new detailthisinh();
                student._masv = Convert.ToString(reader["MASV"]);
                student._mon1 = Convert.ToDouble(reader["MON1"]);
                student._mon2 = Convert.ToDouble(reader["MON2"]);
                student._mon3 = Convert.ToDouble(reader["MON3"]);
                student._ngaysinh = Convert.ToDateTime(reader["NGAYSINH"]).ToShortDateString();
                student._truong = Convert.ToString(reader["TENTRUONG"]);
                student._diemuutien = Convert.ToDouble(reader["DIEMUUTIEN"]);
                student._hoten = Convert.ToString(reader["HOTEN"]);
                student._manganh = Convert.ToInt32(reader["MANGANHDK"]);
                student._diachi = Convert.ToString(reader["DIACHI"]);
                student._cmt = Convert.ToInt64(reader["CMT"]);
                student._dienthoai = Convert.ToInt64(reader["DIENTHOAI"]);
                student._ngaycapnhat = Convert.ToDateTime(reader["NGAYCAPNHAT"]);
                student._manguoidung = Convert.ToString(reader["MANGUOIDUNG"]);
                student._lancapnhat = Convert.ToInt32(reader["LANCAPNHAT"]);
                student._matruong = Convert.ToString(reader["MATRUONG"]);
                member.Add(student);
            }
            return member.ToList();
        }
        public string GetNguoiDungSession(string TAIKHOAN, string MATKHAU)
        {
            try
            {
                OracleConnection connect = connection.connect();
                connect.Open();
                //string sql = "SELECT * FROM NGUOIDUNG WHERE TAIKHOAN ='" + TAIKHOAN + "' AND MATKHAU='" + MATKHAU + "' AND ROWNUM =1";
                //OracleCommand cmd = new OracleCommand(sql, connect);

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connect;
                cmd.CommandText = "GetNguoiDungSession.GetNguoiDungSessionBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("tai_khoan", OracleDbType.Varchar2).Value = TAIKHOAN;
                cmd.Parameters.Add("mat_khau", OracleDbType.Varchar2).Value = MATKHAU;
                cmd.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                int row = 0;
                string UId = "";

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row++;
                }
                if (row == 1)
                {
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        UId = Convert.ToString(reader["MANGUOIDUNG"]);
                    }
                }
                return UId;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public object GetSessionValue()
        {
            return System.Web.HttpContext.Current.Session["MANGUOIDUNG"];
        }
        public void SetSessionValue(string sessionKey)
        {
            System.Web.HttpContext.Current.Session["MANGUOIDUNG"] = sessionKey;
        }
        public List<nguoidung> ShowAllNguoiDung()
        {
            List<nguoidung> member = new List<nguoidung>();
            OracleConnection connect = connection.connect();
            connect.Open();
            string sql = "SELECT NGUOIDUNG.TAIKHOAN,NGUOIDUNG.MATKHAU,NGUOIDUNG.MANGUOIDUNG,QUYEN.TENQUYEN,NGUOIDUNG.EMAIL FROM NGUOIDUNG,QUYEN WHERE NGUOIDUNG.QUYENTRUYCAP = QUYEN.MAQUYEN";
            OracleCommand cmd = new OracleCommand(sql, connect);
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                nguoidung user = new nguoidung();
                user._taikhoan = Convert.ToString(reader["TAIKHOAN"]);
                user._matkhau = Convert.ToString(reader["MATKHAU"]);
                user._manguoidung = Convert.ToInt32(reader["MANGUOIDUNG"]);
                user._quyentruycap = Convert.ToString(reader["TENQUYEN"]);
                user._email = Convert.ToString(reader["EMAIL"]);
                member.Add(user);
            }
            return member.ToList();
        }
        public List<detailtruong> ShowAllTruong()
        {
            List<detailtruong> member = new List<detailtruong>();
            OracleConnection connect = connection.connect();
            connect.Open();
            string sql = "SELECT * FROM TRUONG WHERE LANCAPNHAT=1";
            OracleCommand cmd = new OracleCommand(sql, connect);
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                detailtruong user = new detailtruong();
                user._matruong = Convert.ToString(reader["MATRUONG"]);
                user._tentruong = Convert.ToString(reader["TENTRUONG"]);
                user._email = Convert.ToString(reader["EMAIL"]);
                user._dienthoai = Convert.ToString(reader["DIENTHOAI"]);
                user._manguoidung = Convert.ToString(reader["MANGUOIDUNG"]);
                user._ngaycapnhat = Convert.ToDateTime(reader["NGAYCAPNHAT"]);
                member.Add(user);
            }
            return member.ToList();
        }
        public string InsertThiSinh(string MASV, string HOTEN, double MON1, double MON2, double MON3, double DIEMUUTIEN, int MANGANH, string DIACHI, long CMT, long DIENTHOAI, DateTime NGAYSINH, int MANGUOIDUNG, string MATRUONG)
        {
            OracleConnection connect = connection.connect();
            connect.Open();

            OracleCommand cmdins = new OracleCommand();
            cmdins.Connection = connect;
            cmdins.CommandText = "Check_MASV.Check_MASVBy";
            cmdins.CommandType = CommandType.StoredProcedure;
            cmdins.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = MASV;
            cmdins.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataAdapter da = new OracleDataAdapter(cmdins);
            DataSet ds = new DataSet();
            da.Fill(ds);
            int row = 0;
            string UId = "";

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                row++;
            }
            if (row > 0)
            {
                UId = "Dữ liệu thí sinh đã tồn tại. Mã số :" + MASV;
            }
            else
            {
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connect;
                cmd.CommandText = "InsertThiSinhDiemThi.InsertThiSinhDiemThiBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = MASV;
                cmd.Parameters.Add("mon_1", OracleDbType.Double).Value = MON1;
                cmd.Parameters.Add("mon_2", OracleDbType.Double).Value = MON2;
                cmd.Parameters.Add("mon_3", OracleDbType.Double).Value = MON3;
                cmd.Parameters.Add("diem_uu_tien", OracleDbType.Double).Value = DIEMUUTIEN;
                cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = MATRUONG;
                cmd.Parameters.Add("ma_nguoi_dung", OracleDbType.Int32).Value = MANGUOIDUNG;
                cmd.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now;
                cmd.Parameters.Add("ma_nganh", OracleDbType.Int32).Value = MANGANH;

                OracleCommand cmd1 = new OracleCommand();
                cmd1.Connection = connect;
                cmd1.CommandText = "InsertThiSinhThiSinh.InsertThiSinhDiemThiBy";
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("ho_ten", OracleDbType.Varchar2).Value = HOTEN;
                cmd1.Parameters.Add("dia_chi", OracleDbType.Varchar2).Value = DIACHI;
                cmd1.Parameters.Add("chung_minh_thu", OracleDbType.Int64).Value = CMT;
                cmd1.Parameters.Add("dien_thoai", OracleDbType.Int64).Value = DIENTHOAI;
                cmd1.Parameters.Add("ngay_sinh", OracleDbType.Date).Value = NGAYSINH;
                cmd1.Parameters.Add("ma_nguoi_dung", OracleDbType.Int32).Value = MANGUOIDUNG;
                cmd1.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now;
                cmd1.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = MASV;
                int insert = cmd.ExecuteNonQuery();
                int insert1 = cmd1.ExecuteNonQuery();

                if (insert1 > 0 && insert > 0)
                {
                    UId = "Thêm mới thành công.Mã số :" + MASV;
                }
                else
                {
                    UId = "Thêm mới thất bại.Mã số :" + MASV;
                }
            }
            connect.Close();
            return UId;
        }
        public List<Error> insertThiSinhpro(string[] MASV, string[] HOTEN, double[] MON1, double[] MON2, double[] MON3, double[] DIEMUUTIEN, int[] MANGANH, string[] DIACHI, long[] CMT, long[] DIENTHOAI, DateTime[] NGAYSINH, int MANGUOIDUNG, string[] MATRUONG, long row)
        {
            List<Error> member = new List<Error>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "InsertThiSinh.InsertThiSinhBy";
            cmd.CommandType = CommandType.StoredProcedure;

            Oracle.DataAccess.Client.OracleParameter pa1 = cmd.Parameters.Add("ma_sv", OracleDbType.Varchar2);
            pa1.Direction = ParameterDirection.Input;
            pa1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa1.Value = MASV;

            OracleParameter pa2 = cmd.Parameters.Add("mon_1", OracleDbType.Double);
            pa2.Direction = ParameterDirection.Input;
            pa2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa2.Value = MON1;

            OracleParameter pa3 = cmd.Parameters.Add("mon_2", OracleDbType.Double);
            pa3.Direction = ParameterDirection.Input;
            pa3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa3.Value = MON2;
            OracleParameter pa4 = cmd.Parameters.Add("mon_3", OracleDbType.Double);
            pa4.Direction = ParameterDirection.Input;
            pa4.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa4.Value = MON3;
            OracleParameter pa5 = cmd.Parameters.Add("diem_uu_tien", OracleDbType.Double);
            pa5.Direction = ParameterDirection.Input;
            pa5.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa5.Value = DIEMUUTIEN;
            OracleParameter pa6 = cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2);
            pa6.Direction = ParameterDirection.Input;
            pa6.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa6.Value = MATRUONG;
            OracleParameter pa7 = cmd.Parameters.Add("ma_nganh", OracleDbType.Int32);
            pa7.Direction = ParameterDirection.Input;
            pa7.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa7.Value = MANGANH;
            OracleParameter pa8 = cmd.Parameters.Add("ho_ten", OracleDbType.Varchar2);
            pa8.Direction = ParameterDirection.Input;
            pa8.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa8.Value = HOTEN;
            OracleParameter pa9 = cmd.Parameters.Add("dia_chi", OracleDbType.Varchar2);
            pa9.Direction = ParameterDirection.Input;
            pa9.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa9.Value = DIACHI;
            OracleParameter pa10 = cmd.Parameters.Add("chung_minh_thu", OracleDbType.Int64);
            pa10.Direction = ParameterDirection.Input;
            pa10.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa10.Value = CMT;
            OracleParameter pa11 = cmd.Parameters.Add("dien_thoai", OracleDbType.Int64);
            pa11.Direction = ParameterDirection.Input;
            pa11.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa11.Value = DIENTHOAI;
            OracleParameter pa12 = cmd.Parameters.Add("ngay_sinh", OracleDbType.Date);
            pa12.Direction = ParameterDirection.Input;
            pa12.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            pa12.Value = NGAYSINH;
            cmd.Parameters.Add("ma_nguoi_dung", OracleDbType.Int32).Value = MANGUOIDUNG;//dung ca 2 cho 2 table
            cmd.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now; // Bo di .Su dung ham Now trong oracle
            cmd.Parameters.Add("count_row", OracleDbType.Int64).Value = row;
            cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Error loi = new Error();
                loi._noidung = Convert.ToString(reader["NOIDUNG"]);
                loi._maso = Convert.ToString(reader["MASO"]);
                member.Add(loi);
            }
            connect.Close();
            return member.ToList();
        }
        public List<Error> insertPro(string data, int MANGUOIDUNG)
        {
            List<Error> member = new List<Error>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "InsertThiSinhpro.InsertThiSinhproBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("data", OracleDbType.Clob).Value = data;
            cmd.Parameters.Add("ma_nguoi_dung", OracleDbType.Int32).Value = MANGUOIDUNG;
            cmd.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now;
            cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Error loi = new Error();
                loi._noidung = Convert.ToString(reader["NOIDUNG"]);
                loi._maso = Convert.ToString(reader["MASO"]);
                member.Add(loi);
            }
            connect.Close();
            return member.ToList();

        }
        public object GetSessionTruongValue()
        {
            return System.Web.HttpContext.Current.Session["MATRUONG"];
        }
        public void SetSessionTruongValue(int sessionKey)
        {
            System.Web.HttpContext.Current.Session["MATRUONG"] = sessionKey;
        }
        public bool UpdateThiSinh(detailthisinh dthisinh, string MANGUOIDUNG, DateTime date)
        {
            bool Upd = false;
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd2 = new OracleCommand();
            cmd2.Connection = connect;
            cmd2.CommandText = "UpdateThiSinhDiemThi.UpdateThiSinhDiemThiBy";
            cmd2.CommandType = CommandType.StoredProcedure;
            cmd2.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = dthisinh._masv;

            OracleCommand cmd3 = new OracleCommand();
            cmd3.Connection = connect;
            cmd3.CommandText = "UpdateThiSinhThiSinh.UpdateThiSinhThiSinhBy";
            cmd3.CommandType = CommandType.StoredProcedure;
            cmd3.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = dthisinh._masv;

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "InsertThiSinhDiemThi.InsertThiSinhDiemThiBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = dthisinh._masv;
            cmd.Parameters.Add("mon_1", OracleDbType.Double).Value = dthisinh._mon1;
            cmd.Parameters.Add("mon_2", OracleDbType.Double).Value = dthisinh._mon2;
            cmd.Parameters.Add("mon_3", OracleDbType.Double).Value = dthisinh._mon3;
            cmd.Parameters.Add("diem_uu_tien", OracleDbType.Double).Value = dthisinh._diemuutien;
            cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = dthisinh._matruong;
            cmd.Parameters.Add("ma_nguoi_dung", OracleDbType.Int32).Value = MANGUOIDUNG;
            cmd.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now;
            cmd.Parameters.Add("ma_nganh", OracleDbType.Int32).Value = dthisinh._manganh;

            OracleCommand cmd1 = new OracleCommand();
            cmd1.Connection = connect;
            cmd1.CommandText = "InsertThiSinhThiSinh.InsertThiSinhDiemThiBy";
            cmd1.CommandType = CommandType.StoredProcedure;
            cmd1.Parameters.Add("ho_ten", OracleDbType.Varchar2).Value = dthisinh._hoten;
            cmd1.Parameters.Add("dia_chi", OracleDbType.Varchar2).Value = dthisinh._diachi;
            cmd1.Parameters.Add("chung_minh_thu", OracleDbType.Int64).Value = dthisinh._cmt;
            cmd1.Parameters.Add("dien_thoai", OracleDbType.Int64).Value = dthisinh._dienthoai;
            cmd1.Parameters.Add("ngay_sinh", OracleDbType.Date).Value = date;
            cmd1.Parameters.Add("ma_nguoi_dung", OracleDbType.Int32).Value = MANGUOIDUNG;
            cmd1.Parameters.Add("ngay_cap_nhat", OracleDbType.Date).Value = DateTime.Now;
            cmd1.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = dthisinh._masv;




            int update1 = cmd2.ExecuteNonQuery();
            int update2 = cmd3.ExecuteNonQuery();
            int insert = cmd.ExecuteNonQuery();
            int insert1 = cmd1.ExecuteNonQuery();

            if (insert > 0 && insert1 > 0 && update1 > 0 && update2 > 0)
            {
                Upd = true;
            }
            return Upd;
        }
        public List<detailthisinh> SearchDetailThiSinh(string MATRUONG, string MASV, string HOTEN, long CMT)
        {
            List<detailthisinh> member = new List<detailthisinh>();
            OracleConnection connect = connection.connect();
            connect.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "SearchDetailThiSinh.SearchDetailThiSinhBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = MATRUONG;
            cmd.Parameters.Add("ma_sinhvien", OracleDbType.Varchar2).Value = MASV;
            cmd.Parameters.Add("ho_ten", OracleDbType.Varchar2).Value = HOTEN;
            cmd.Parameters.Add("chung_minhthu", OracleDbType.Int64).Value = CMT;
            cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                detailthisinh student = new detailthisinh();
                student._masv = Convert.ToString(reader["MASV"]);
                student._mon1 = Convert.ToDouble(reader["MON1"]);
                student._mon2 = Convert.ToDouble(reader["MON2"]);
                student._mon3 = Convert.ToDouble(reader["MON3"]);
                student._ngaysinh = Convert.ToDateTime(reader["NGAYSINH"]).ToShortDateString();
                student._truong = Convert.ToString(reader["TENTRUONG"]);
                student._diemuutien = Convert.ToDouble(reader["DIEMUUTIEN"]);
                student._hoten = Convert.ToString(reader["HOTEN"]);
                student._manganh = Convert.ToInt32(reader["MANGANHDK"]);
                student._diachi = Convert.ToString(reader["DIACHI"]);
                student._cmt = Convert.ToInt64(reader["CMT"]);
                student._dienthoai = Convert.ToInt64(reader["DIENTHOAI"]);
                student._ngaycapnhat = Convert.ToDateTime(reader["NGAYCAPNHAT"]);
                student._manguoidung = Convert.ToString(reader["MANGUOIDUNG"]);
                student._lancapnhat = Convert.ToInt32(reader["LANCAPNHAT"]);
                student._matruong = Convert.ToString(reader["MATRUONG"]);
                member.Add(student);
            }
            return member.ToList();
        }
        public List<quyen> GetQuyen()
        {
            List<quyen> member = new List<quyen>();
            OracleConnection connect = connection.connect();
            connect.Open();
            //string sql = "SELECT * FROM QUYEN";
            //OracleCommand cmd = new OracleCommand(sql, connect);
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "GetQuyen.GetQuyenBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                quyen permission = new quyen();
                permission._maquyen = Convert.ToString(reader["MAQUYEN"]);
                permission._tenquyen = Convert.ToString(reader["TENQUYEN"]);
                member.Add(permission);
            }
            return member.ToList();
        }
        public int GetQuyenTruyCap(int ID)
        {
            try
            {
                OracleConnection connect = connection.connect();
                connect.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connect;
                cmd.CommandText = "GetQuyenTruyCap.GetQuyenTruyCapBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("ma_nguoi_dung", OracleDbType.Int32).Value = ID;
                cmd.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                int row = 0;
                int UId = 0;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row++;
                }
                if (row == 1)
                {
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        UId = Convert.ToInt32(reader["QUYENTRUYCAP"]);
                    }
                }
                return UId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool CheckIdTruong(string MATRUONG)
        {
            try
            {
                OracleConnection connect = connection.connect();
                connect.Open();
                //string sql = "SELECT MATRUONG FROM TRUONG WHERE MATRUONG ='" + MATRUONG + "'AND LANCAPNHAT=1 AND ROWNUM =1";
                //OracleCommand cmd = new OracleCommand(sql, connect);
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connect;
                cmd.CommandText = "CheckIdTruong.CheckIdTruongBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = MATRUONG;
                cmd.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                int row = 0;
                bool UId = false;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row++;
                }
                if (row == 1)
                {
                    UId = true;

                }
                return UId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool CheckThiSinh(string MATHISINH)
        {
            try
            {
                OracleConnection connect = connection.connect();
                connect.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connect;
                cmd.CommandText = "CheckThiSinh.CheckThiSinhBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("ma_sv", OracleDbType.Varchar2).Value = MATHISINH;
                cmd.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                int row = 0;
                bool UId = false;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row++;
                }
                if (row > 0)
                {
                    UId = true;

                }
                return UId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<detailtruong> ShowLichSuTruong(string MATRUONG)
        {
            List<detailtruong> member = new List<detailtruong>();
            OracleConnection connect = connection.connect();
            connect.Open();
            //string sql = "SELECT * FROM TRUONG WHERE MATRUONG='" + MATRUONG + "' ORDER BY NGAYCAPNHAT DESC";
            //OracleCommand cmd = new OracleCommand(sql, connect);

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "ShowLichSuTruong.ShowLichSuTruongBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ma_truong", OracleDbType.Varchar2).Value = MATRUONG;
            cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                detailtruong user = new detailtruong();
                user._matruong = Convert.ToString(reader["MATRUONG"]);
                user._tentruong = Convert.ToString(reader["TENTRUONG"]);
                user._email = Convert.ToString(reader["EMAIL"]);
                user._dienthoai = Convert.ToString(reader["DIENTHOAI"]);
                user._manguoidung = Convert.ToString(reader["MANGUOIDUNG"]);
                user._ngaycapnhat = Convert.ToDateTime(reader["NGAYCAPNHAT"]);
                member.Add(user);
            }
            return member.ToList();
        }
        public List<detailthisinh> ShowLichSuThiSinh(string MASV)
        {
            List<detailthisinh> member = new List<detailthisinh>();
            OracleConnection connect = connection.connect();
            connect.Open();
            //string sql = "SELECT DIEMTHI.MASV,DIEMTHI.MON1,DIEMTHI.MON2,DIEMTHI.MON3,DIEMTHI.DIEMUUTIEN,DIEMTHI.MANGANHDK,DIEMTHI.NGAYCAPNHAT,DIEMTHI.MANGUOIDUNG,DIEMTHI.LANCAPNHAT,DIEMTHI.MATRUONG,THISINH.HOTEN,THISINH.NGAYSINH,TRUONG.TENTRUONG,THISINH.DIACHI,THISINH.CMT,THISINH.DIENTHOAI FROM THISINH,DIEMTHI,TRUONG WHERE DIEMTHI.MASV=THISINH.MASV AND DIEMTHI.MATRUONG=TRUONG.MATRUONG AND TRUONG.LANCAPNHAT=1 AND DIEMTHI.NGAYCAPNHAT=THISINH.NGAYCAPNHAT AND DIEMTHI.MASV='" + MASV + "' ORDER BY DIEMTHI.NGAYCAPNHAT DESC";
            //OracleCommand cmd = new OracleCommand(sql, connect);
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connect;
            cmd.CommandText = "ShowLichSuThiSinh.ShowLichSuThiSinhBy";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ma_sinhvien", OracleDbType.Varchar2).Value = MASV;
            cmd.Parameters.Add("cur_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                detailthisinh student = new detailthisinh();
                student._masv = Convert.ToString(reader["MASV"]);
                student._mon1 = Convert.ToDouble(reader["MON1"]);
                student._mon2 = Convert.ToDouble(reader["MON2"]);
                student._mon3 = Convert.ToDouble(reader["MON3"]);
                student._ngaysinh = Convert.ToDateTime(reader["NGAYSINH"]).ToShortDateString();
                student._truong = Convert.ToString(reader["TENTRUONG"]);
                student._diemuutien = Convert.ToDouble(reader["DIEMUUTIEN"]);
                student._hoten = Convert.ToString(reader["HOTEN"]);
                student._manganh = Convert.ToInt32(reader["MANGANHDK"]);
                student._diachi = Convert.ToString(reader["DIACHI"]);
                student._cmt = Convert.ToInt64(reader["CMT"]);
                student._dienthoai = Convert.ToInt64(reader["DIENTHOAI"]);
                student._ngaycapnhat = Convert.ToDateTime(reader["NGAYCAPNHAT"]);
                student._manguoidung = Convert.ToString(reader["MANGUOIDUNG"]);
                student._lancapnhat = Convert.ToInt32(reader["LANCAPNHAT"]);
                student._matruong = Convert.ToString(reader["MATRUONG"]);
                member.Add(student);
            }
            return member.ToList();
        }
        public int AutoIncrementID()
        {
            try
            {
                List<nguoidung> member = new List<nguoidung>();
                OracleConnection connect = connection.connect();
                connect.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connect;
                cmd.CommandText = "AutoIncrementID.AutoIncrementIDBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("cursor_Search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                int row = 0;
                int UId = 0;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row++;
                }
                if (row == 1)
                {
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        UId = Convert.ToInt32(reader["MANGUOIDUNG"]);
                    }
                }
                return UId + 1;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool CheckNguoiDung(string TAIKHOAN)
        {
            try
            {
                OracleConnection connect = connection.connect();
                connect.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connect;
                cmd.CommandText = "CheckNguoiDung.CheckNguoiDungBy";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("tai_khoan", OracleDbType.Varchar2).Value = TAIKHOAN;
                cmd.Parameters.Add("cur_search", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                int row = 0;
                bool UId = false;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row++;
                }
                if (row > 0)
                {
                    UId = true;

                }
                return UId;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
